﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Colaboradores.Models;

namespace Colaboradores.Persistencia
{
    public class FuncionarioExistenteException : Exception { }
    public class FuncionarioNaoExistenteException : Exception { }

    public class Persistencia_Func
    {
        public static void Inserir(Cls_Func pFunc)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);

            SqlCommand cInsert = new SqlCommand("INSERT INTO TAB_FUNCIONARIO (MAT, NOM, DEP, CAR) VALUES (@MAT, @NOM, @DEP, @CAR)", con);
            cInsert.Parameters.AddWithValue("MAT", pFunc.Mat);
            cInsert.Parameters.AddWithValue("NOM", pFunc.Nom);
            cInsert.Parameters.AddWithValue("DEP", pFunc.Depto.Cod);
            cInsert.Parameters.AddWithValue("CAR", pFunc.Cargo.Cod);

            Cls_Func oFunc = ConsultarPorMatricula(pFunc.Mat);

            if (oFunc != null)
            {
                pFunc.Nom = oFunc.Nom;
                pFunc.Depto.Dsc = Persistencia_Depto.ConsultarPorCodigo(oFunc.Depto.Cod).Dsc;
                pFunc.Cargo.Dsc = Persistencia_Cargo.ConsultarPorCodigo(oFunc.Cargo.Cod).Dsc;
                throw new FuncionarioExistenteException();
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cInsert.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public static void Alterar(Cls_Func pFunc)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);

            SqlCommand cAlterar = new SqlCommand("UPDATE TAB_FUNCIONARIO SET NOM = @NOM, DEP = @DEP, CAR = @CAR WHERE MAT = @MAT", con);
            cAlterar.Parameters.AddWithValue("MAT", pFunc.Mat);
            cAlterar.Parameters.AddWithValue("NOM", pFunc.Nom);
            cAlterar.Parameters.AddWithValue("DEP", pFunc.Depto.Cod);
            cAlterar.Parameters.AddWithValue("CAR", pFunc.Cargo.Cod);

            Cls_Func oFunc = ConsultarPorMatricula(pFunc.Mat);

            if (oFunc == null)
            {
                throw new FuncionarioNaoExistenteException();
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cAlterar.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public static void Excluir(Cls_Func pFunc)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);

            SqlCommand cExcluir = new SqlCommand("DELETE FROM TAB_FUNCIONARIO WHERE MAT = @MAT", con);
            cExcluir.Parameters.AddWithValue("MAT", pFunc.Mat);

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                cExcluir.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public static IList<Cls_Func> Listar()
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);
            SqlCommand cConsulta = new SqlCommand("SELECT MAT, NOM, DEP, CAR FROM TAB_FUNCIONARIO", con);
            Cls_Func func;
            IList<Cls_Func> lstFunc = new List<Cls_Func>();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                SqlDataReader rConsulta = cConsulta.ExecuteReader();

                while (rConsulta.Read())
                {
                    func = new Cls_Func(rConsulta.GetInt32(0), 
                                        rConsulta.GetString(1), 
                                        Persistencia_Depto.ConsultarPorCodigo(rConsulta.GetInt32(2)),
                                        Persistencia_Cargo.ConsultarPorCodigo(rConsulta.GetInt32(3)));

                    lstFunc.Add(func);
                }
                rConsulta.Close();
            }
            finally
            {
                con.Close();
            }
            return lstFunc;
        }

        public static Cls_Func ConsultarPorMatricula(int pMat)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);
            SqlCommand cConsulta = new SqlCommand("SELECT NOM, DEP, CAR FROM TAB_FUNCIONARIO WHERE MAT = @MAT", con);
            cConsulta.Parameters.AddWithValue("MAT", pMat);

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                SqlDataReader rConsulta = cConsulta.ExecuteReader();

                if (rConsulta.Read())
                {
                    return new Cls_Func(pMat, 
                                        rConsulta.GetString(0),
                                        Persistencia_Depto.ConsultarPorCodigo(rConsulta.GetInt32(1)),
                                        Persistencia_Cargo.ConsultarPorCodigo(rConsulta.GetInt32(2)));
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }

        public static IList<Cls_Func> ConsultarPorNome(String pNom)
        {
            SqlConnection con = new SqlConnection(Conexao.ConnectionString);
            SqlCommand cConsulta = new SqlCommand("SELECT MAT, NOM, DEP, CAR FROM TAB_FUNCIONARIO WHERE NOM LIKE '%' + @NOM + '%'", con);
            cConsulta.Parameters.AddWithValue("NOM", pNom);
            Cls_Func func;
            IList<Cls_Func> lstFunc = new List<Cls_Func>();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                SqlDataReader rConsulta = cConsulta.ExecuteReader();

                while (rConsulta.Read())
                {
                    func = new Cls_Func(rConsulta.GetInt32(0), 
                                        rConsulta.GetString(1),
                                        Persistencia_Depto.ConsultarPorCodigo(rConsulta.GetInt32(2)),
                                        Persistencia_Cargo.ConsultarPorCodigo(rConsulta.GetInt32(3)));

                    lstFunc.Add(func);
                }
                rConsulta.Close();
            }
            finally
            {
                con.Close();
            }
            return lstFunc;
        }
    }
}